<?php

class UserModel extends BaseModel
{
    public static function addActivity($userId, $platformId)
    {
        $sql = 'INSERT INTO activity (user_id, platform_id, created_at, created_date) VALUES (:user_id, :platform_id, NOW(), CURRENT_DATE())';
        $command = Yii::app()->db->createCommand($sql);
        $command->bindParam(':user_id', $userId);
        $command->bindParam(':platform_id', $platformId);
        $command->execute();
    }

    public static function calcAvgSession($date, $platformId = null)
    {
        $sql = 'SELECT ROUND(AVG(duration)/60)
                FROM
                    (
                    SELECT user_id, platform_id, session_id, UNIX_TIMESTAMP(MAX(created_at)) - UNIX_TIMESTAMP(MIN(created_at)) + 60 AS duration
                    FROM
                    (
                        SELECT
                            *,
                            IF(user_id = @prev_user AND DATE_SUB(created_at, INTERVAL 5 MINUTE) < @prev_time, @session_id, @session_id := @session_id + 1) as session_id,
                            @prev_user := user_id,
                            @prev_time := created_at
                        FROM activity, (SELECT @session_id := 0, @prev_user := 0, @prev_time := "") t
                        WHERE created_date = :date
                        ORDER BY user_id, created_at
                    ) t
                    GROUP BY session_id
                ) t';
        if($platformId !== null)
        {
            $sql .= ' WHERE platform_id = '.$platformId;
        }
        $command = Yii::app()->db->createCommand($sql);
        $command->bindParam(':date', $date);
        return $command->queryScalar();
    }
}
